import math
import time
from datetime import datetime, timedelta

import pandas as pd
import requests


def fetch_api_data(page_no, page_size):
    """分页获取API数据，包含错误处理"""
    url = 'http://10.135.13.2:48082/admin-api/infra/videoplatform/regions'
    headers = {
        'User-Agent': 'Apifox/1.0.0 (https://apifox.com)',
        'Content-Type': 'application/json'
    }
    payload = {
        "pageNo": page_no,
        "pageSize": page_size,
        "treeCode": "0",
        "env": 1
    }

    try:
        response = requests.post(url, headers=headers, json=payload)
        response.raise_for_status()  # 检查HTTP错误
        json_data = response.json()

        if json_data.get('code') != 0:
            raise ValueError(f"API返回错误码: {json_data.get('code')}")

        data = json_data.get('data', {}).get('data', {})
        return data.get('list', []), data.get('total', 0)

    except requests.exceptions.RequestException as e:
        print(f"请求失败: {e}")
        return [], 0
    except ValueError as ve:
        print(ve)
        return [], 0


def process_date_fields(data_list):
    """处理API返回的日期字段（如updateTime/createTime）"""
    for item in data_list:
        date_fields = ['updateTime', 'createTime']
        for field in date_fields:
            if field in item:
                timestamp = item[field]
                # 处理毫秒级时间戳
                if isinstance(timestamp, (int, float)) and timestamp > 0:
                    try:
                        dt = datetime.utcfromtimestamp(timestamp / 1000)
                        item[field] = dt.strftime('%Y-%m-%d')
                    except (ValueError, OSError):
                        item[field] = None
                # 校验字符串日期格式
                elif isinstance(timestamp, str):
                    try:
                        datetime.strptime(timestamp, '%Y-%m-%d')
                    except ValueError:
                        item[field] = None
    return data_list


def add_sync_date(data_list):
    """为每条数据添加同步日期字段（前一天）"""
    sync_date = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')
    for item in data_list:
        item['sync_date'] = sync_date  # 新增字段
    return data_list


def save_to_excel(data_list):
    """保存数据到Excel并确保日期格式正确，同步日期字段放在首列[1,3,4](@ref)"""
    if not data_list:
        print("无有效数据可保存")
        return

    try:
        df = pd.DataFrame(data_list)

        # 确保sync_date列存在
        if 'sync_date' not in df.columns:
            sync_date = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')
            df['sync_date'] = sync_date

        # 重新排列列顺序，将sync_date放在第一列[6,7](@ref)
        columns = ['sync_date'] + [col for col in df.columns if col != 'sync_date']
        df = df[columns]

        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        filename = f"api_data_{timestamp}.xlsx"

        # 使用ExcelWriter设置日期格式[1,5](@ref)
        with pd.ExcelWriter(filename, engine='openpyxl', datetime_format='yyyy-mm-dd') as writer:
            df.to_excel(writer, index=False)
        print(f"数据已保存至 {filename}，共 {len(df)} 条记录 | 同步日期字段已置于首列")

    except Exception as e:
        print(f"保存Excel失败: {e}")


def main():
    # 计算前一天日期（格式: 2025-08-24）
    sync_date = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')
    print(f"同步日期字段值: {sync_date}")

    # 初始请求获取总记录数
    _, total_records = fetch_api_data(1, 1000)
    if total_records == 0:
        print("未获取到有效数据，请检查API连接")
        return

    print(f"总记录数: {total_records}")
    page_size = 100  # 每页大小
    total_pages = math.ceil(total_records / page_size)
    all_data = []

    # 分页获取数据
    for page in range(1, total_pages + 1):
        page_data, _ = fetch_api_data(page, page_size)
        if page_data:
            processed_data = process_date_fields(page_data)  # 处理原始日期字段
            processed_data = add_sync_date(processed_data)  # 添加同步日期字段
            all_data.extend(processed_data)
            print(f"已获取第 {page}/{total_pages} 页 | 本页 {len(page_data)} 条")
        time.sleep(0.5)  # 避免请求过频

    # 保存结果
    save_to_excel(all_data)


if __name__ == "__main__":
    main()